package com.tsc.dateinfo;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Iterator;

import com.tsc.dateinfo.data.CouponMoaArea;
import com.tsc.dateinfo.data.DataCouponMoa;
import com.tsc.dateinfo.data.IData;

public class DatabaseHelper {
	
	private static final String ConnectionString = 
			"jdbc:mysql://ec2-54-178-198-1.ap-northeast-1.compute.amazonaws.com:3306/firstdream";
	private static final String DB_ID = "root";
	private static final String DB_PW = "exitexit1";
	private static final String JdbcClass = "com.mysql.jdbc.Driver";
	
//	public static void main(String[] args)
//	{
//		try {
//			Class.forName(JdbcClass);
//			Connection conn = DriverManager.getConnection(ConnectionString, DB_ID, DB_PW);
//			
//		} catch (ClassNotFoundException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		} catch (SQLException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}
//	}

	public void deleteCouponMoa(CouponMoaArea area) {
		try{
			Class.forName(JdbcClass);
			Connection conn = DriverManager.getConnection(ConnectionString, DB_ID, DB_PW);
			
			String query = 
					"delete from SocialCommerce where area=?";
			
			PreparedStatement stmt = conn.prepareStatement(query);
			stmt.setString(1, area.areaName);
			stmt.execute();
			stmt.close();
			
		}catch(Exception e)
		{}
	}
	
	public void insertCouponMoa(ArrayList<IData> list) {
		try {
			Class.forName(JdbcClass);
			Connection conn = DriverManager.getConnection(ConnectionString, DB_ID, DB_PW);
			String[] strSplited;
			Iterator<IData> iter = list.iterator();
			int i = 1;
			
			while(iter.hasNext()) {
				long start = -1;
				long end = -1;
				DataCouponMoa data = (DataCouponMoa)iter.next();
				
				String title = null;
				try {
					title = new String(data.getTitle().getBytes("UTF8"), "UTF-8");
				}catch(Exception ex){}
				int orgPrice = data.getOrigPrice();
				int dicPrice = data.getDiscPrice();
				String startDate = data.getStartDate();
				Calendar cal;
				if(startDate != null) {
					strSplited = startDate.split("-");
					cal = Calendar.getInstance();
					cal.set(Calendar.YEAR, Integer.parseInt(strSplited[0]));
					cal.set(Calendar.MONTH, Integer.parseInt(strSplited[1]));
					cal.set(Calendar.DAY_OF_MONTH, Integer.parseInt(strSplited[2]));
					start = cal.getTimeInMillis();
				}
							
				String endDate = data.getEndDate();
				if(endDate != null) {
					strSplited = endDate.split("-");
					cal = Calendar.getInstance();
					cal.set(Calendar.YEAR, Integer.parseInt(strSplited[0]));
					cal.set(Calendar.MONTH, Integer.parseInt(strSplited[1]));
					cal.set(Calendar.DAY_OF_MONTH, Integer.parseInt(strSplited[2]));
					end = cal.getTimeInMillis();
				}
				float latitude = data.getLatitude();
				float longitude = data.getLongitude();
				String area=null;
				try{
					area= new String(data.getArea().getBytes("UTF8"), "UTF-8");
				}catch(Exception ex){}
				String text = null;
				try {
					text= new String(data.getText().getBytes("UTF8"), "UTF-8");
				}catch(Exception ex){}
				String imgUrl = null;
				try {
					imgUrl = new String(data.getImgUrl().getBytes("UTF8"), "UTF-8");
				}catch(Exception ex) {}
				String url = null;
				try {
					url = new String(data.getUrl().getBytes("UTF8"), "UTF-8");
				}catch(Exception ex){}
				String aidInfo = null;
				try {
					aidInfo = new String(data.getAidInfo().getBytes("UTF8"), "UTF-8");
				}catch(Exception ex) {}
				
				String query = 
						"insert into SocialCommerce(title, orig_price, disc_price, start_date, end_date, latitude, longitude, area, text, imgurl, url, aid_info) values(?,?,?,?,?,?,?,?,?,?,?,?)";
				
				PreparedStatement stmt = conn.prepareStatement(query);
				stmt.setString(1, title);
				stmt.setInt(2, orgPrice);
				stmt.setInt(3, dicPrice);
				if(start != -1)
					stmt.setDate(4, new Date(start));
				else
					stmt.setDate(4, null);
				if(end != -1)
					stmt.setDate(5, new Date(end));
				else
					stmt.setDate(5, null);
				stmt.setFloat(6, latitude);
				stmt.setFloat(7, longitude);
				stmt.setString(8, area);
				stmt.setString(9, text);
				stmt.setString(10, imgUrl);
				stmt.setString(11, url);
				stmt.setString(12, aidInfo);
					
				stmt.execute();
				stmt.close();
				
				
				ServerLog.log("debug", "success insert"+i+"items");
				i++;
			}
			
			
			
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			ServerLog.log("exception", "insert failed. class net found exception : " + e.getMessage());
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			ServerLog.log("exception", "insert failed. sql exception : " + e.getMessage());
			e.printStackTrace();
		}
	}

}
